﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace CreateMenu.DataAcces
{	
    public class AccionData
    {
        public class customTblAccion
        {
            public int accion { get; set; }
            public string nombreAccion { get; set; }
            public string descripcion { get; set; }
        }

        public class relacion
        {
            public int ACCION { get; set; }
            public string NOMBREACCION { get; set; }
            public string DESCRIPCION { get; set; }
            public int PADRE { get; set; }
            public string DESCRIPCIONPadre { get; set; }
        }
        private static string cadenaConexion
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();                    
            }
        }

        public static string save(int accion, string nombreaccion, string descripcion, string tipo)
        {
            string respuesta = "";
            using (SqlConnection con = new SqlConnection(cadenaConexion))
            {
                string query = "INSERT INTO ACCION (nombreaccion,descripcion,tipo) VALUES (@nombreAccion,@descripcion,@tipo)";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    //cmd.Parameters.Add(new SqlParameter("@accionId", accion));
                    cmd.Parameters.Add(new SqlParameter("@nombreAccion", nombreaccion));
                    cmd.Parameters.Add(new SqlParameter("@descripcion", descripcion));
                    cmd.Parameters.Add(new SqlParameter("@tipo", tipo));
                    if (cmd.Connection.State == System.Data.ConnectionState.Closed) { cmd.Connection.Open(); }
                    try
                    {
                        cmd.ExecuteNonQuery();
                        respuesta = "Exito";
                    }
                    catch (Exception ex)
                    {
                        respuesta = "Error al insertar en la tabla Accion.\n" + ex.Message.ToString();
                    }
                    if (cmd.Connection.State == System.Data.ConnectionState.Open) { cmd.Connection.Close(); }
                }
            }
            return respuesta;
        }

        public static string registrarParentesco(int accion, int padre)
        {
            string respuesta = "";
            using (SqlConnection con = new SqlConnection(cadenaConexion))
            {
                string query = "INSERT INTO PARENTESCO (accion,padre) VALUES (@accionId,@padreId)";//"UPDATE PARENTESCO SET PADRE = @padreId WHERE ACCION = @accionId AND PADRE =  @padreId";//"INSERT INTO PARENTESCO (accion,padre) VALUES (@accionId,@padreId)";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@accionId", accion));
                    cmd.Parameters.Add(new SqlParameter("@padreId", padre));
                    if (cmd.Connection.State == System.Data.ConnectionState.Closed) { cmd.Connection.Open(); }
                    try
                    {
                        cmd.ExecuteNonQuery();
                        respuesta = "Exito";
                    }
                    catch (Exception ex)
                    {
                        respuesta = "Error al ACTUALIZAR en la tabla Parentesco.\n" + ex.Message.ToString();
                    }
                    if (cmd.Connection.State == System.Data.ConnectionState.Open) { cmd.Connection.Close(); }
                }
            }
            return respuesta;
        }

        public static DataSet getListAcciones(string tipo)	
	    {
		    DataSet data = new DataSet();
		    using(SqlConnection con = new SqlConnection(cadenaConexion))
		    {
			    string query = "SELECT accion,nombreaccion,descripcion,tipo FROM ACCION where tipo = @tipo ORDER BY accion DESC";
			    using(SqlDataAdapter  adapter  = new SqlDataAdapter(query,con))
			    {
				    adapter.SelectCommand.CommandType = CommandType.Text;
				    adapter.SelectCommand.Parameters.Add(new SqlParameter("@tipo",tipo));
                    try
                    {
                        adapter.Fill(data);
                    }
                    catch (Exception)
                    {
                        
                    }				    
				}
		    }
		    return data;
	    }

        public static List<relacion> getRelacionModulos()
        {
            
            List<relacion> lst = new List<relacion>();
            string query =
                        "SELECT PARENTESCO.ACCION, ACCION.NOMBREACCION, ACCION.DESCRIPCION, PARENTESCO.PADRE,(SELECT ACCION.DESCRIPCION FROM ACCION WHERE ACCION = PARENTESCO.PADRE) AS DESCRIPCIONPadre" +
                        " FROM PARENTESCO INNER JOIN ACCION ON PARENTESCO.ACCION = ACCION.ACCION GROUP BY ACCION.NOMBREACCION, ACCION.DESCRIPCION, PARENTESCO.ACCION, PARENTESCO.PADRE" +
                        " ORDER BY PARENTESCO.ACCION DESC";
            using (SqlConnection con = new SqlConnection(cadenaConexion))
            {
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.CommandType = System.Data.CommandType.Text;                    
                    if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); }
                    try
                    {
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                relacion r = new relacion();
                                r.ACCION = reader.GetInt32(0);
                                r.NOMBREACCION = reader.GetString(1);
                                r.DESCRIPCION = reader.GetString(2);
                                r.PADRE = reader.GetInt32(3);
                                r.DESCRIPCIONPadre = reader.GetString(4);
                                lst.Add(r);
                            }                            
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); }
                }                
            }
            return lst;
        }
        public static customTblAccion getAccionById(int accion)
        {
            customTblAccion objAccion = new customTblAccion();
            using (SqlConnection con = new SqlConnection(cadenaConexion))
            {
                string query = "SELECT accion,nombreaccion,descripcion,tipo FROM ACCION where accion = @numeroaccion";
                using (SqlCommand cmd = new SqlCommand(query,con))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.Add(new SqlParameter("@numeroaccion", accion));
                    if (cmd.Connection.State == ConnectionState.Closed) { cmd.Connection.Open(); }
                    try
                    {
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                objAccion.accion = reader.GetInt32(0);
                                objAccion.nombreAccion = reader.GetString(1);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    if (cmd.Connection.State == ConnectionState.Open) { cmd.Connection.Close(); }
                }
            }
            return objAccion;
        }

        public static int getUltimaAccion()
        {
            int ultima = 0;
            using (SqlConnection con = new SqlConnection(cadenaConexion))
            {
                string query = "SELECT TOP 1 ACCION FROM ACCION ORDER BY ACCION DESC";
                using (SqlCommand cmd = new SqlCommand(query,con))
                {
                    if (cmd.Connection.State == System.Data.ConnectionState.Closed) { cmd.Connection.Open(); }
                    try
                    {
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                ultima = Convert.ToInt32(reader.GetInt32(0));    
                            }                            
                            ultima++;
                        }
                        else
                        {
                            ultima = 1;
                        }
                    }
                    catch (Exception ex)
                    {
                        
                    }
                    if (cmd.Connection.State == System.Data.ConnectionState.Open) { cmd.Connection.Close(); }
                }
            }
            return ultima;
        }

        //public static string updatePadre()
        //{
        //    string respuesta = "";
        //    string query = "UPDATE PARENTESCO";
        //}
    }
}
